import os
from common import waterfall_connection
from common import close_connection
from common import factory_connection

#type ='pickup'
type = 'shipment'
sql_list = ['wf','pn','peach','meow','colorful','newlook','star']

home_dir = os.path.expanduser("~")
in_file_path = os.path.join(home_dir, 'Downloads', f'{type}.diff')
lake_diff = os.path.join(home_dir, 'Downloads', f'{type}_lake_diff.tsv')
lake_status_diff = os.path.join(home_dir, 'Downloads', f'{type}_lake_status_diff.tsv')
waterfall_diff = os.path.join(home_dir, 'Downloads', f'{type}_waterfall_diff.tsv')
waterfall_status_diff = os.path.join(home_dir, 'Downloads', f'{type}_waterfall_status_diff.tsv')

pickup_mapping = {
    'PENDING': '1',
    'SIGNFOFF': '2',
    'VERIFY': '3',
    'RECEIVED': '4',
    'CANCEL': '5'
}
shipment_mapping = {
    'PENDING': '1',
    'ORDERING': '2',
    'TOPICK': '3',
    'PICKING': '4',
    'PICKED': '5',
    'PACKING': '6',
    'PACKED': '7',
    'SHIPPED': '8'
}




def check_pickup_state(pickup_no):
    value = None
    global order_no
    global status, code
    connection = waterfall_connection()
    cursor = connection.cursor()
    cursor.execute(
        f"SELECT ff.code,fp.status,fp.order_no FROM wf_factory.ffl_{type} fp LEFT JOIN wf_factory.fty_factory ff on fp.factory_id = ff.id WHERE fp.`no` = '{pickup_no} and fp.flag & 16 != 16'")
    result = cursor.fetchone()
    close_connection(connection)
    if result:
        code = result[0]
        status = result[1]
        order_no = result[2]
    else:
        connection = waterfall_connection()
        cursor = connection.cursor()
        for item in sql_list:
            cursor.execute(
                f"SELECT ff.code,fp.status,fp.order_no FROM {item}_factory.ffl_{type} fp LEFT JOIN {item}_factory.fty_factory ff on fp.factory_id = ff.id WHERE fp.`no` = '{pickup_no} and fp.flag & 16 != 16'")
            result = cursor.fetchone()
            if result:
                print(item)
                code = result[0]
                status = result[1]
                order_no = result[2]
                print(f"{code}\t{status}")
                break
        close_connection(connection)
    if code:
        connection = factory_connection(code)
        cursor = connection.cursor()
        cursor.execute(f"SELECT `status` FROM opr_{type} WHERE ch_{type}_no = '{pickup_no}' and flag & 512 != 512")
        result = cursor.fetchone()
        close_connection(connection)
        # if type == 'pickup':
        #     mapped_value = pickup_mapping.get(status, 'Default')
        # else:
        #     mapped_value = shipment_mapping.get(status, 'Default')
        if result:
            return True
            #value = result[0]
        else:
            print('查询结果为空')
            return False
        # if value is not None:
        #     print(f'{mapped_value}\t{value}')
        #     if mapped_value == value:
        #         return True
        #     else:
        #         return False
        # else:
        #    return False
    else:
        print("未找到 工厂代码")
        return False





def check_waterfall(pickup_no):
    connection = waterfall_connection()
    cursor = connection.cursor()
    for item in sql_list:
        cursor.execute(
            f"SELECT status FROM {item}_factory.ffl_{type}  WHERE `no` = '{pickup_no}' and flag & 16 != 16")
        result = cursor.fetchone()
        if result:
            print(result)
            return True
    close_connection(connection)
    return False

def check_lake(code,pickup_no):
    connection = factory_connection(code)
    cursor = connection.cursor()
    cursor.execute(f"SELECT `status` FROM opr_{type} WHERE ch_{type}_no = '{pickup_no}' and flag & 512 != 512")
    result = cursor.fetchone()
    close_connection(connection)
    if result:
        return True
    else:
        return False


def get_order_no(pickup_no):
    connection = waterfall_connection()
    cursor = connection.cursor()
    for item in sql_list:
        cursor.execute(
            f"SELECT order_no FROM {item}_factory.ffl_{type}  WHERE `no` = '{pickup_no}'")
        result = cursor.fetchone()
        if result:
            order_no = result[0]
            print(order_no)
            return order_no
    close_connection(connection)


def get_channel(pickup_no):
    connection = waterfall_connection()
    cursor = connection.cursor()
    for item in sql_list:
        cursor.execute(
            f"SELECT status FROM {item}_factory.ffl_{type}  WHERE `no` = '{pickup_no}' and flag & 16 != 16")
        result = cursor.fetchone()
        if result:
            return item
    close_connection(connection)



with (open(in_file_path, 'r') as input_file,
      open(lake_diff, 'w') as output_lake,
      open(lake_status_diff, 'w') as output_status_lake,
      open(waterfall_diff, 'w') as output_waterfall,
      open(waterfall_status_diff, 'w') as output_status_waterfall):
    for line in input_file:
        parts = line.split('\t')
        if len(parts) >= 3:
            pickup_no = parts[1]
            state = parts[2]
            order_no = get_order_no(pickup_no)
            print(f"{pickup_no}\t{state}")
        if line.startswith('<'):
            # if check_pickup_state(pickup_no):
            #     output_status_lake.write(f"{order_no}\t{line.lstrip('< ')}")
            # else:
            #     output_lake.write(f"{order_no}\t{line.lstrip('< ')}")
            if check_waterfall(pickup_no):
                output_status_lake.write(f"{order_no}\t{line.lstrip('< ')}")
            else:
                output_lake.write(f"{order_no}\t{line.lstrip('< ')}")

        elif line.startswith('>'):
            # if check_pickup_state(pickup_no):
            #     output_waterfall.write(f"{order_no}\t{line.lstrip('> ')}")
            # else:
            #     output_status_waterfall.write(f"{order_no}\t{line.lstrip('> ')}")
            values = line.lstrip('> ').split('\t')
            code = values[0]
            print(code)
            if check_lake(code,pickup_no):
               output_status_waterfall.write(f"{order_no}\t{line.lstrip('> ')}")
            else:
               #channel = get_channel(pickup_no)
               output_waterfall.write(f"{order_no}\t{line.lstrip('> ')}")

